﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using QLKN.DataAccess;
using System.Windows.Forms;
using QLKN.Report;

namespace QLKN.GUI
{
    public partial class frmQLNhanvien : Form
    {
        public frmQLNhanvien()
        {
            InitializeComponent();
        }
        SqlConnection conn;
        SqlCommand cmd;
        SqlDataAdapter da;
        DataSet ds;
        public DataTable LayGioiTinh()
        {
            conn = Data.getConnection();
            conn.Open();
            string sql = @"SELECT DISTINCT nvgioitinh, TenGT=CASE "
                                            + "nvgioitinh when 'true' then N'Nam'"
                                            + "WHEN 'false' then N'Nữ' END FROM nhanvien";
            DataTable dt = Data.getDataTable(sql, conn);
            return dt;
        }

        public DataTable LayDSPhongban()
        {
            conn = Data.getConnection();
            conn.Open();
            string sql = @"SELECT * FROM phongban";
            DataTable dt = Data.getDataTable(sql, conn);
            return dt;
        }
        public DataTable LayDSQuyen()
        {
            conn = Data.getConnection();
            conn.Open();
            string sql = @"SELECT * FROM phanquyen";
            DataTable dt = Data.getDataTable(sql, conn);
            return dt;
        }

        public DataSet laydsquyen()
        {
            conn = Data.getConnection();
            conn.Open();
            string sql = @"SELECT * FROM phanquyen";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            DataTable dt = new DataTable();
            da.Fill(ds, "dsquyen");
            dt = ds.Tables["dsquyen"];
            return ds;
        }
        private void frmQLNhanvien_Load(object sender, EventArgs e)
        {
            cmbTKphongban.DataSource = LayDSPhongban();
            cmbTKphongban.SelectedIndex=0;
            cmbTKphongban.DisplayMember = "pbtenphongban";
            cmbTKphongban.ValueMember = "pbid";
            
            cmbGioitinh.DataSource = LayGioiTinh();
            cmbGioitinh.DisplayMember = "TenGT";
            cmbGioitinh.ValueMember = "nvgioitinh";

            cmbPhongban.DataSource = LayDSPhongban();
            cmbPhongban.DisplayMember = "pbtenphongban";
            cmbPhongban.ValueMember = "pbid";

            laydsquyen();
            cmbQuyen.DataSource = ds.Tables["dsquyen"];
            cmbQuyen.DisplayMember = "pquyen";
            cmbQuyen.ValueMember = "pid";


            //colGioitinh.DataSource = LayGioiTinh();
            //colGioitinh.DisplayMember = "TenGT";
            //colGioitinh.ValueMember = "nvgioitinh";

            colPhongban.DataSource = LayDSPhongban();
            colPhongban.DisplayMember = "pbtenphongban";
            colPhongban.ValueMember = "pbid";

            colQuyen.DataSource = LayDSQuyen();
            colQuyen.DisplayMember = "pquyen";
            colQuyen.ValueMember = "pid";

            conn = Data.getConnection();
           // cmd = new SqlCommand("SELECT * FROM nhanvien", conn);
            cmd = new SqlCommand("SELECT nvid,nvtendangnhap,nvmatkhau,nvtendaydu,(CASE WHEN nvgioitinh='True' THEN N'Nam' ELSE N'Nữ' END) AS nvgioitinh,nvngaysinh,nvemail,nvsdt,nvtrangthai,pbid,pid FROM nhanvien", conn);
            conn.Open();
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "nhanvien");
            SqlCommandBuilder cmb = new SqlCommandBuilder(da);
            dgv_QLNV.DataSource = ds.Tables["nhanvien"];
            conn.Close();
            btnLuu.Enabled = false;
            btnKhongluu.Enabled = false;
        }
        //public bool KiemTraThem()
        //{



        //}


        private void btnCapnhat_Click(object sender, EventArgs e)
        {
            try
            {
                if (MessageBox.Show("Bạn có cập nhật nhân viên không?", "THÔNG BÁO", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {

                    DataTable dt1 = new DataTable();
                    dt1 = ds.Tables["nhanvien"];
                    int i = dgv_QLNV.CurrentRow.Index;
                    dt1.Rows[i]["nvtendangnhap"] = txtTendangnhap.Text;
                    dt1.Rows[i]["nvmatkhau"] = txtMatkhau.Text;
                    dt1.Rows[i]["nvtendaydu"] = txtTendaydu.Text;
                    dt1.Rows[i]["nvgioitinh"] = cmbGioitinh.SelectedValue;
                    dt1.Rows[i]["nvngaysinh"] = dTP_Ngaysinh.Value;
                    dt1.Rows[i]["nvemail"] = txtEmail.Text;
                    dt1.Rows[i]["nvsdt"] = txtSDT.Text;
                    dt1.Rows[i]["nvtrangthai"] = rdHoatdong.Checked;
                    dt1.Rows[i]["pbid"] = cmbPhongban.SelectedValue;
                    dt1.Rows[i]["pid"] = cmbQuyen.SelectedValue;

                    da.Update(ds, "nhanvien");
                    frmQLNhanvien_Load(sender, e);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }

        private void btnLuu_Click(object sender, EventArgs e)
        {
            try
            {
                if (MessageBox.Show("Bạn có muốn them không?", "THÔNG BÁO", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    DataTable dt = new DataTable();
                    dt = ds.Tables["nhanvien"];
                    DataRow r = dt.NewRow();
                    r["nvtendangnhap"] = txtTendangnhap.Text;
                    r["nvmatkhau"] = txtMatkhau.Text;
                    r["nvtendaydu"] = txtTendaydu.Text;
                    r["nvgioitinh"] = cmbGioitinh.SelectedValue;
                    r["nvngaysinh"] = dTP_Ngaysinh.Value;
                    r["nvemail"] = txtEmail.Text;
                    r["nvsdt"] = txtSDT.Text;
                    r["nvtrangthai"] = rdHoatdong.Checked;
                    r["pbid"] = cmbPhongban.SelectedValue;
                    r["pid"] = cmbQuyen.SelectedValue;
                    dt.Rows.Add(r);
                    da.Update(ds, "nhanvien");
                    frmQLNhanvien_Load(sender, e);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            int i = dgv_QLNV.CurrentRow.Index;
            try
            {
                if (MessageBox.Show("Bạn có muốn xoá nhân viên này không?", "THÔNG BÁO", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    ds.Tables["nhanvien"].Rows[i].Delete();
                    da.Update(ds, "nhanvien");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private void txt_TennvTK_KeyUp(object sender, KeyEventArgs e)
        {

            string sql = "SELECT * FROM nhanvien WHERE nvtendaydu LIKE N'%" + txt_TennvTK.Text + "%' and pbid='"+cmbTKphongban.SelectedValue+"'";
            conn = Data.getConnection();
            cmd = new SqlCommand(sql, conn);
            conn.Open();
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "nhanvien");
            SqlCommandBuilder cmb = new SqlCommandBuilder(da);
            dgv_QLNV.DataSource = ds.Tables["nhanvien"];
            conn.Close();

        }

        private void dgv_QLNV_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                int dong = e.RowIndex;
                txtTendangnhap.Text = dgv_QLNV.Rows[dong].Cells["colTendangnhap"].Value.ToString();
                txtMatkhau.Text = dgv_QLNV.Rows[dong].Cells["colMatkhau"].Value.ToString();
                txtTendaydu.Text = dgv_QLNV.Rows[dong].Cells["colTendaydu"].Value.ToString();
                cmbGioitinh.SelectedValue = dgv_QLNV.Rows[dong].Cells["colGioitinh"].Value.ToString();
                dTP_Ngaysinh.Text = dgv_QLNV.Rows[dong].Cells["colNgaysinh"].Value.ToString();
                txtEmail.Text = dgv_QLNV.Rows[dong].Cells["colEmail"].Value.ToString();
                txtSDT.Text = dgv_QLNV.Rows[dong].Cells["colSDT"].Value.ToString();

                if (dgv_QLNV.Rows[dong].Cells["colTrangthai"].Value.ToString().Equals("True"))
                {
                    rdHoatdong.Checked = true;
                    rdKhongHD.Checked = false;
                }
                else
                {
                    rdHoatdong.Checked = false;
                    rdKhongHD.Checked = true;
                }
                cmbPhongban.SelectedValue = dgv_QLNV.Rows[dong].Cells["colPhongban"].Value.ToString();
                cmbQuyen.SelectedValue = dgv_QLNV.Rows[dong].Cells["colQuyen"].Value.ToString();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }

        private void btnThem_Click(object sender, EventArgs e)
        {
            btnThem.Enabled = false;
            btnXoa.Enabled = false;
            btnCapnhat.Enabled = false;
            btnThoat.Enabled = false;

            btnKhongluu.Enabled = true;
            btnLuu.Enabled = true;

            cmbGioitinh.SelectedValue = true;
            txtTendangnhap.Text = "";
            txtMatkhau.Text = "";
            txtTendaydu.Text = "";


            txtEmail.Text = "";
            txtSDT.Text = "";
            rdHoatdong.Checked = true;
            cmbQuyen.SelectedValue = "003";


            // txtNvid.Text = "";
            txtTendangnhap.Focus();
        }

        private void btnKhongluu_Click(object sender, EventArgs e)
        {
            btnThem.Enabled = true;
            btnXoa.Enabled = true;
            btnCapnhat.Enabled = true;
            btnThoat.Enabled = true;

            btnKhongluu.Enabled = false;
            btnLuu.Enabled = false;
        }

        private void btnThoat_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void btnIn_Click(object sender, EventArgs e)
        {

            MyDataset mds = new MyDataset();
            int feilds = dgv_QLNV.Rows.Count;
            for (int i = 0; i <= feilds - 2; i++)
            {
                mds.Tables[0].Rows.Add(new object[] {   dgv_QLNV[0,i].Value.ToString(),
                                                        dgv_QLNV[1,i].Value.ToString(),
                                                        dgv_QLNV[2,i].Value.ToString(),
                                                        dgv_QLNV[3,i].Value.ToString(),
                                                        dgv_QLNV[4,i].Value.ToString(),
                                                        dgv_QLNV[5,i].Value.ToString(),
                                                        dgv_QLNV[6,i].Value.ToString(),
                                                        dgv_QLNV[7,i].Value.ToString(),
                                                        dgv_QLNV[8,i].Value.ToString(),
                                                        dgv_QLNV[9,i].Value.ToString(),
                                                        dgv_QLNV[10,i].Value.ToString()
                                                        
                                                        
            });
            }
                rpNhanvien nv = new rpNhanvien(mds);
                nv.Show();

        }

        private void cmbTKphongban_SelectionChangeCommitted(object sender, EventArgs e)
        {
            conn = Data.getConnection();
            if (cmbTKphongban.SelectedIndex==0)
            {
                cmd = new SqlCommand("SELECT nvid,nvtendangnhap,nvmatkhau,nvtendaydu,(CASE WHEN nvgioitinh='True' THEN N'Nam' ELSE N'Nữ' END) AS nvgioitinh,nvngaysinh,nvemail,nvsdt,nvtrangthai,pbid,pid FROM nhanvien", conn); 
            }
            else
                cmd = new SqlCommand("SELECT nvid,nvtendangnhap,nvmatkhau,nvtendaydu,(CASE WHEN nvgioitinh='True' THEN N'Nam' ELSE N'Nữ' END) AS nvgioitinh,nvngaysinh,nvemail,nvsdt,nvtrangthai,pbid,pid FROM nhanvien where pbid='" + cmbTKphongban.SelectedValue + "'", conn);
            conn.Open();
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "nhanvien");
            SqlCommandBuilder cmb = new SqlCommandBuilder(da);
            dgv_QLNV.DataSource = ds.Tables["nhanvien"];
            conn.Close();
        }
    }
}

